﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Security;
using System.Text;
using Emoney.DataAccess;
using LiuYan.Base;
using LiuYan.Entity;


namespace LiuYan.Service
{
    public class GoodService
    {
        /// <summary>
        /// 编辑商品
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public int Edit(TB_Goods entity)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@Id",SqlDbType.Int),
                new SqlParameter("@Name",SqlDbType.VarChar,150),
                new SqlParameter("@Img1",SqlDbType.VarChar,150),
                new SqlParameter("@Img2",SqlDbType.VarChar,150),
                new SqlParameter("@Img3",SqlDbType.VarChar,150),
                new SqlParameter("@Img4",SqlDbType.VarChar,150),
                new SqlParameter("@Img5",SqlDbType.VarChar,150),
                new SqlParameter("@Count",SqlDbType.Int), 
                new SqlParameter("@PayCountTrue",SqlDbType.Int),
                new SqlParameter("@Des",SqlDbType.Text),
                new SqlParameter("@Price",SqlDbType.Money),
                new SqlParameter("@DiscountPrice",SqlDbType.Money),
                new SqlParameter("@Sort",SqlDbType.Int),
                new SqlParameter("@IsShow",SqlDbType.Int),
                new SqlParameter("@Date",SqlDbType.DateTime), 
                new SqlParameter("@PayCount",SqlDbType.Int), 
                new SqlParameter("@Freight",SqlDbType.Money), 
                new SqlParameter("@Xj",SqlDbType.Int), 
            };
            parameters[0].Value = entity.Id;
            parameters[1].Value = entity.Name;
            parameters[2].Value = entity.Img1;
            parameters[3].Value = entity.Img2;
            parameters[4].Value = entity.Img3;
            parameters[5].Value = entity.Img4;
            parameters[6].Value = entity.Img5;
            parameters[7].Value = entity.Count;
            parameters[8].Value = entity.PayCountTrue;
            parameters[9].Value = entity.Des;
            parameters[10].Value = entity.Price;
            parameters[11].Value = entity.DiscountPrice;
            parameters[12].Value = entity.Sort;
            parameters[13].Value = entity.IsShow;
            parameters[14].Value = entity.Date;
            parameters[15].Value = entity.PayCount;
            parameters[16].Value = entity.Freight;
            parameters[17].Value = entity.Xj;
 string  sql="";
            if (entity.Id != 0)
            {
                sql = "update [dbo].[TB_Goods] set Xj=@Xj,Name=@Name,img1=@Img1,img2=@Img2,img3=@Img3,img4=@Img4,img5=@Img5,[count]=@Count,PayCountTrue=@PayCountTrue,paycount=@PayCount,[des]=@Des,price=@Price,discountprice=@DiscountPrice,sort=@Sort,isshow=@IsShow,freight=@Freight where id=@Id";
            }
            else
            {
                sql = "insert into TB_Goods(Xj,Name,Img1,Img2,Img3,Img4,Img5,[Count],PayCountTrue,PayCount,Des,Price,DiscountPrice,Sort,IsShow,[Date],del,Freight)values(@Xj,@Name,@Img1,@Img2,@Img3,@Img4,@Img5,@Count,@PayCountTrue,@PayCount,@Des,@Price,@DiscountPrice,@Sort,@IsShow,@Date,0,@Freight) select @@identity";
                return Convert.ToInt32(SqlHelper.ExecuteScalar(Connection.conStr, CommandType.Text, sql, parameters));
            }
            return SqlHelper.ExecuteNonQuery(Connection.conStr, CommandType.Text, sql, parameters);
        }

        /// <summary>
        /// get goods list
        /// </summary>
        /// <param name="pageCount"></param>
        /// <param name="pageIndex"></param>
        /// <param name="name"></param>
        /// <param name="sort"></param>
        /// <returns></returns>
        public DataSet GetList(int pageCount, int pageIndex, string name,int sort)
        {
            SqlParameter parameter=new SqlParameter("@name",SqlDbType.VarChar,80);
            parameter.Value = name;
            string where = "";
            where = sort == 0 ? where : " and sort=" + sort+" ";
            where = name == "" ? where : (where + " and name like  N'%'+@name+'%'");
            string sql = "select count(*) as count from [dbo].[TB_Goods] where  xj=0 and del=0 " + where +
                         " select top(" + pageCount + ")*,(select Name from TB_Sort where TB_Sort.Id=TB_Goods.Sort) as ShortName," +
                         "(select Gid from [dbo].[TB_RxGood] where TB_RxGood.Gid=TB_Goods.Id) as RxGood " +
                         " from [dbo].[TB_Goods] where id not in(select top(" + pageIndex + ")Id from [dbo].[TB_Goods] where xj=0 and del=0 " + where + " order by id desc)and  xj=0 and del=0 " + where + " order by id desc ";
            var set= SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql,parameter);
            set.Tables[0].TableName = "count";
            set.Tables[1].TableName = "data";
            return set;
        }

      
        /// <summary>
        /// 根据分类获取商品
        /// </summary>
        /// <param name="sort"></param>
        /// <returns></returns>
        public DataTable GetList(int sort)
        {
            string sql = "select top(2) Id,Img1,Name,Price,DiscountPrice,PayCountTrue,PayCount  from [dbo].[TB_Goods]  where isshow=0 and xj=0 and del=0  and  sort=" + sort;
            return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql).Tables[0];
        }
        /// <summary>
        /// 根据分类获取折扣商品
        /// </summary>
        /// <param name="sort"></param>
        /// <returns></returns>
        public DataTable GetDiscountList(int sort)
        {
            string sql = "select  Id,Img1,Name,Price,DiscountPrice,PayCountTrue,PayCount,Sort from dbo.TB_Goods where del=0 and xj=0 and discountprice>0 and sort=" + sort;
            return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql).Tables[0];
        }

        /// <summary>
        /// 获取前几条折扣商品
        /// </summary>
        /// <param name="count"></param>
        /// <returns></returns>
        public DataTable GetDiscountGoods(int count)
        {
            string sql = "select top("+count+") Id,Name,Img1, [Price],[DiscountPrice] from [dbo].[TB_Goods] where [DiscountPrice]>0  and IsShow=0 and xj=0 and del=0 order by Id desc";
            return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql).Tables[0];
        }

        /// <summary>
        /// 编辑商品属性
        /// </summary>
        /// <param name="list"></param>
        /// <param name="gid"></param>
        public void EditAttribute(List<TB_Good_Property> list,int gid)
        {
            if (list.Count == 0)
            {
                SqlHelper.ExecuteNonQuery(Connection.conStr, CommandType.Text, "delete TB_Good_Property where gid='"+gid+"'");
            }
            string sql = " begin transaction ";
            for (int i = 0; i < list.Count; i++)
            {
                if (list[i].Id == 0)
                {
                    sql += " insert into TB_Good_Property (gid,name,value,del,price)values('" + gid + "','" +
                           list[i].Name + "','" + list[i].Value + "','0','" + list[i].Price + "') ";
                }
                else
                {
                    sql += " update TB_Good_Property set name='" + list[i].Name + "', " + "value='" + list[i].Value + "', price=" + "'" + list[i].Price + "'"+"where id="+list[i].Id+" ";
                }
            }
            sql += " commit transaction ";
            SqlHelper.ExecuteNonQuery(Connection.conStr, CommandType.Text, sql);
        }

        /// <summary>
        /// 获取商品属性
        /// </summary>
        /// <param name="gid"></param>
        /// <returns></returns>
        public DataTable GetAttribute(int gid)
        {
            string sql = "select * from [dbo].[TB_Good_Property] where gid=" + gid + " and del=0";
            return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql).Tables[0];
        }

        /// <summary>
        /// 获取一个商品信息
        /// </summary>
        /// <param name="gid"></param>
        /// <returns></returns>
        public DataSet GetOneGood(int gid)
        {
            string sql = "select Id,Name,Img1,Img2,Img3,Img4,Img5,Freight,[Count],[PayCountTrue],[PayCount],[Des],[Price],[DiscountPrice] from [dbo].[TB_Goods] where id=" + gid + " and del=0 " +
                        "select top(50) Date,Img1,Img2,Img3,Text,Rnk,PlName,(select NcName from TB_User where TB_User.Id=TB_Evaluate.UserId) as NcName " +
                         " from [dbo].[TB_Evaluate] where del=0 and gid=" + gid + " order by id desc " +
                         "select Id,gid,name,value,price from [dbo].[TB_Good_Property] where gid="+gid+" and del=0";
            return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable GetgPjInfo(int id)
        {
            string sql = "select Id,Name,Img1,Img2,Img3 from dbo.TB_Goods where id="+id;
            return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql).Tables[0];
        }

        /// <summary>
        /// 获取新品列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageCount"></param>
        /// <returns></returns>
        public DataSet GetXpGood(int pageCount, int pageIndex, string name)
        {
            SqlParameter parameter = new SqlParameter("@name", SqlDbType.VarChar, 80);
            parameter.Value = name;
            string where = "";
            where = name == "" ? where : (" and name like  N'%'+@name+'%'");
            string sql = "select count(*) as count from TB_XpGood as a, [dbo].[TB_Goods] as b where a.gId=b.id and b.del=0 "+where +
                " select top(" + pageCount + ")a.Id,a.Gid,b.Name,b.Img1,b.Price,b.DiscountPrice,b.Count,b.PayCountTrue,b.PayCount,b.Date " +
                         "from dbo.TB_XpGood as a,dbo.TB_Goods as b "+
                         "where a.Id not in (select top(" + pageIndex + ") Id from TB_XpGood order by id desc) and a.Del=0 and a.Gid=b.Id and b.Del=0 "+where+" order by a.Id desc";
            var set = SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql, parameter);
            set.Tables[0].TableName = "count";
            set.Tables[1].TableName = "data";
            return set;
        }
        /// <summary>
        /// 获取热销商品列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageCount"></param>
        /// <returns></returns>
        public DataSet  GetRxGood(int pageCount, int pageIndex, string name)
        {
            SqlParameter parameter = new SqlParameter("@name", SqlDbType.VarChar, 80);
            parameter.Value = name;
            string where = "";
            where = name == "" ? where : (" and name like  N'%'+@name+'%'");
            string sql = "select count(*) as count from TB_RxGood as a, [dbo].[TB_Goods] as b where a.gId=b.id and b.del=0 " + where +
                "select top(" + pageCount + ")a.Id,a.Gid,b.Name,b.Img1,b.Price,b.DiscountPrice,b.Count,b.PayCountTrue,b.PayCount,b.Date " +
                         "from dbo.TB_RxGood as a,dbo.TB_Goods as b " +
                         "where a.Id not in (select top(" + pageIndex + ") Id from TB_RxGood order by id desc) and a.Del=0 and a.Gid=b.Id and b.Del=0 "+where+" order by a.Id desc";
            var set = SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql, parameter);
            set.Tables[0].TableName = "count";
            set.Tables[1].TableName = "data";
            return set;
        }

      
        public DataTable GetGoodT(int sid)
        {
            string sql = "select Id,Name from dbo.TB_Goods where Del=0 and sort="+sid;
            return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql).Tables[0];
        }

        /// <summary>
        /// 添加新品和热销
        /// </summary>
        /// <param name="gid"></param>
        /// <param name="tb"></param>
        /// <returns></returns>
        public int EditRxXp(int gid)
        {
            string sql = "select count(*) as count from TB_RxGood where gid=" + gid;
            if (Convert.ToInt32(SqlHelper.ExecuteScalar(Connection.conStr, CommandType.Text, sql)) > 0)
            {
                return -1;
            }
            sql = "insert into TB_RxGood(gid,del)values('" + gid + "',0)";
            return SqlHelper.ExecuteNonQuery(Connection.conStr, CommandType.Text, sql);
        }

        /// <summary>
        /// 商城热销产品和新品
        /// </summary>
        /// <param name="gid"></param>
        /// <param name="tb"></param>
        /// <returns></returns>
        public int DelRx(int gid)
        {
            string sql = "delete from TB_RxGood where gid=" + gid;
            return SqlHelper.ExecuteNonQuery(Connection.conStr, CommandType.Text, sql);
        }


        public string AttName(int id)
        {
            string sql = "select Name,Value,Price from dbo.TB_Good_Property where id="+id;
            var table = SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql).Tables[0];
            if (table.Rows.Count> 0)
            {
                return (table.Rows[0]["Name"].ToString() + " " + table.Rows[0]["Value"].ToString() + " " + table.Rows[0]["Price"].ToString()+"元");
            }
            return "";
        }

        /// <summary>
        /// 返回搜索列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageCount"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public DataTable GetSearchList(int pageIndex, int pageCount, string name)
        {
            SqlParameter parameter = new SqlParameter("@name",SqlDbType.VarChar,100);
            parameter.Value = name;
            string sql = "select top(" + pageCount + ")Id,Img1,Name,Price,PayCountTrue,PayCount  from [dbo].[TB_Goods]  where id not in" +
" (select top(" + pageIndex + ")Id from [dbo].[TB_Goods] order by id desc)" +
" and isshow=0 and xj=0 and del=0 and name like  N'%'+@name+'%' order by id desc";
            return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql,parameter).Tables[0];
        }

        /// <summary>
        /// 获取一条商品数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataTable GetOneGoodData(int id)
        {
            string sql = "select *," +
                          "(select Gid from [dbo].[TB_RxGood] where TB_RxGood.Gid=TB_Goods.Id) as RxGood from  [dbo].[TB_Goods] where id="+id;
            return SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql).Tables[0];
        }

        /// <summary>
        /// 获取商品名称
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetGoodName(int id)
        {
            string sql = "select Name from TB_Goods where id="+id;
            return SqlHelper.ExecuteScalar(Connection.conStr, CommandType.Text, sql).ToString();
        }
        /// <summary>
        /// 获取该商品的评价
        /// </summary>
        /// <param name="pageCount"></param>
        /// <param name="pageIndex"></param>
        /// <param name="gid"></param>
        /// <returns></returns>
        public DataSet PingJia(int pageCount, int pageIndex,int gid)
        {

            string sql = "select count(*) as count from TB_Evaluate where gid=" +gid+
                         " select top(" + pageCount + ") * from  dbo.TB_Evaluate " +
                         "where Id not in(select top(" + pageIndex + ") Id from TB_Evaluate where  del=0 and Gid=" + gid + " " +
                         "order by Id desc)and del=0  and Gid=" + gid + " order by Id desc ";
            var set = SqlHelper.ExecuteDataset(Connection.conStr, CommandType.Text, sql);
            set.Tables[0].TableName = "count";
            set.Tables[1].TableName = "data";
            return set;
        }

    }
}
